﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using DevExpress.Web.ASPxTreeList;
using System.Drawing;

public partial class WebUserControl_ControlBaoCaoCayPhanCap : System.Web.UI.UserControl
{
    MethodClassCommon common;
    SqlConnection con;
    string str = BCDataCenter.Configuration.CurrentConnectionString;
    SqlTransaction tran;
    BCDataCenter.DataAccessLayer.DAL dal;
    string error = "";
   
    SqlCommand CreateTable1,INSERT_user_ReportDoanhSoTuyenDuoi_LAN2, DELETE_user_ReportDoanhSoTuyenDuoi, INSERT_user_ReportDoanhSoTuyenDuoi1, DELETE_TABLE1, DELETE_TABLE2, DELETE_TABLE3, DELETE_TABLE4, CreateTable, Update_DoanhSo, InsertTable, CREATE_TABLE_TempOfftake1, InsertTableTempOfftake1, InsertTableTempOfftake2, CREATE_TABLE_TempOfftake2;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!IsPostBack)
            {
                txtToDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            }
            else
            {
                try
                {
                    if (ViewState["db"] != null)
                    {
                        DataTable db = (DataTable)ViewState["db"];
                        ASPxTreeList1.DataSource = db;
                        ASPxTreeList1.DataBind();
                        TinhTong();
                    }
                }
                catch
                {
                    return;
                }
            }
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
    void TinhTong()
    {
        ASPxTreeList1.Summary.Clear();
        TreeListSummaryItem soluong = new TreeListSummaryItem();
        soluong.FieldName = "TVV";
        soluong.ShowInColumn = "TVV";
        soluong.SummaryType = DevExpress.Data.SummaryItemType.Count;
        soluong.DisplayFormat = "{0:Số lượng tư vấn viên cấp dưới  ,0}";
        ASPxTreeList1.Summary.Add(soluong);
        TreeListSummaryItem doanhso = new TreeListSummaryItem();
        doanhso.FieldName = "DoanhSo";
        doanhso.ShowInColumn = "DoanhSo";
        doanhso.SummaryType = DevExpress.Data.SummaryItemType.Sum;
        doanhso.DisplayFormat = "{0:Tổng doanh số  #,###}";
        ASPxTreeList1.Summary.Add(doanhso);
    }
    void ShowGroup()
    {
        
        ASPxTreeList1.Settings.ShowGroupFooter = true;
        ASPxTreeList1.Settings.ShowFooter = true;
        ASPxTreeList1.Styles.GroupFooter.Font.Bold = true;
        ASPxTreeList1.Styles.GroupFooter.ForeColor = Color.Blue;
        ASPxTreeList1.Styles.GroupFooter.BackColor = Color.Yellow;
        
    }
    void QuyTrinh9Buoc()
    {
        DateTime dateTest;
        DateTime dateTest1;
        System.Globalization.DateTimeFormatInfo sFormat = new System.Globalization.DateTimeFormatInfo();
        sFormat.ShortDatePattern = "dd/MM/yyyy";
        dateTest = Convert.ToDateTime(txtFromDate.Text, sFormat);
        dateTest1 = Convert.ToDateTime(txtToDate.Text, sFormat);
        string frDate = String.Format("{0:yyyy/MM/dd}", dateTest);
        string toDate = String.Format("{0:yyyy/MM/dd}", dateTest1);
        int FromMonth = Convert.ToInt32(txtFromDate.Text.Substring(3, 2));
        int FromYear = Convert.ToInt32(txtFromDate.Text.Substring(6, 4));
        int ToMonth = Convert.ToInt32(txtToDate.Text.Substring(3, 2));
        int ToYear = Convert.ToInt32(txtToDate.Text.Substring(6, 4));
        double max = ToYear * 12 + ToMonth;
        double i = FromYear * 12 + FromMonth;
       
            string a_AdnCode = "";
            string selADNCode = "";
           
            if (Session["usertype"].ToString() == "ad")
            {
                a_AdnCode = common.GetDataDepend("GET_ANDCODE_ADMIN", "parameter", "BC");
            }
            else if (Session["usertype"].ToString() == "sa")
            {
                a_AdnCode = common.GetDataDepend("GET_ANDCODE_ADMIN", "parameter", "BC" + Session["Sitecode"].ToString());
            }
            else if (Session["usertype"].ToString() == "us")
            {
                a_AdnCode = common.GetDataDepend("GET_ANDCODE_ADMIN", "parameter", Session["Salesp_key_Logon"].ToString());
            }
            try
            {
                selADNCode = common.GetDataDependCayPhanCap("GET_ADNCODE_PHAN_CAP", "inputSalespKey", txtTuVanVien.Text.Trim(), "Max_AdnCode", a_AdnCode);
                con = new SqlConnection(str);
                con.Open();
                tran = con.BeginTransaction();
                DELETE_TABLE1 = new SqlCommand();
                DELETE_TABLE1.Connection = con;
                DELETE_TABLE2 = new SqlCommand();
                DELETE_TABLE2.Connection = con;
                DELETE_TABLE3 = new SqlCommand();
                DELETE_TABLE3.Connection = con;
                CreateTable = new SqlCommand();
                CreateTable.Connection = con;
                InsertTable = new SqlCommand();
                InsertTable.Connection = con;
                CREATE_TABLE_TempOfftake1 = new SqlCommand();
                CREATE_TABLE_TempOfftake1.Connection = con;
                InsertTableTempOfftake1 = new SqlCommand();
                InsertTableTempOfftake1.Connection = con;
                CREATE_TABLE_TempOfftake2 = new SqlCommand();
                CREATE_TABLE_TempOfftake2.Connection = con;
                InsertTableTempOfftake2 = new SqlCommand();
                InsertTableTempOfftake2.Connection = con;
                Update_DoanhSo = new SqlCommand();
                Update_DoanhSo.Connection = con;
                    //kiểm tra và xóa table 
                    DELETE_TABLE1.CommandText = "DELETE_TABLE";
                    DELETE_TABLE1.CommandType = CommandType.StoredProcedure;
                    DELETE_TABLE1.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                    DELETE_TABLE1.Transaction = tran;
                    DELETE_TABLE1.ExecuteNonQuery();

                   
                    
                    //tạo table @user_ReportDoanhSoTuyenDuoi
                    CreateTable.CommandText = "CREATE_TABLE_CAY_PHAN_CAP";
                    CreateTable.CommandType = CommandType.StoredProcedure;
                    CreateTable.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                    CreateTable.Transaction = tran;
                    CreateTable.ExecuteNonQuery();

                    //insert data vào table @user_ReportDoanhSoTuyenDuoi
                    InsertTable.CommandText = "InsertTablePhanCap";
                    InsertTable.CommandType = CommandType.StoredProcedure;
                    InsertTable.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                    InsertTable.Parameters.Add(new SqlParameter("@selAdnCode", selADNCode));
                    InsertTable.Transaction = tran;
                    InsertTable.ExecuteNonQuery();

                    //kiểm tra và xóa table user_TempOfftake1
                    DELETE_TABLE2.CommandText = "DELETE_TABLE";
                    DELETE_TABLE2.CommandType = CommandType.StoredProcedure;
                    DELETE_TABLE2.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_TempOfftake1"));
                    DELETE_TABLE2.Transaction = tran;
                    DELETE_TABLE2.ExecuteNonQuery();

                    //tạo table @user_TempOfftake1
                    CREATE_TABLE_TempOfftake1.CommandText = "CREATE_TABLE_TempOfftake1";
                    CREATE_TABLE_TempOfftake1.CommandType = CommandType.StoredProcedure;
                    CREATE_TABLE_TempOfftake1.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_TempOfftake1"));
                    CREATE_TABLE_TempOfftake1.Transaction = tran;
                    CREATE_TABLE_TempOfftake1.ExecuteNonQuery();

                    while (i <= max)
                    {
                        //insert vào table @User_TempOfftake1
                        InsertTableTempOfftake1 = new SqlCommand();
                        InsertTableTempOfftake1.Connection = con;
                        InsertTableTempOfftake1.CommandText = "InsertTableTempOfftake1";
                        InsertTableTempOfftake1.CommandType = CommandType.StoredProcedure;
                        InsertTableTempOfftake1.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_TempOfftake1"));
                        if (FromMonth < 10)
                        {
                            InsertTableTempOfftake1.Parameters.Add(new SqlParameter("@month", "0" + Convert.ToString(FromMonth) + Convert.ToString(FromYear)));
                        }
                        else
                        {
                            InsertTableTempOfftake1.Parameters.Add(new SqlParameter("@month", Convert.ToString(FromMonth) + Convert.ToString(FromYear)));
                        }
                     
                        InsertTableTempOfftake1.Parameters.Add(new SqlParameter("@frDate", frDate));
                        InsertTableTempOfftake1.Parameters.Add(new SqlParameter("@toDate", toDate));
                        InsertTableTempOfftake1.Parameters.Add(new SqlParameter("@user_ReportDoanhSoTuyenDuoi", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                        InsertTableTempOfftake1.Transaction = tran;
                        try
                        {
                            InsertTableTempOfftake1.ExecuteNonQuery();
                        }
                        catch
                        {
                           
                           
                        }
                        FromMonth = FromMonth + 1;
                        if (FromMonth > 12)
                        {
                            FromMonth = 1;
                            FromYear = FromYear + 1;
                        }
                        i = FromYear * 12 + FromMonth;

                    }
                    //kiểm tra và xóa table user_TempOfftake2
                    DELETE_TABLE3.CommandText = "DELETE_TABLE";
                    DELETE_TABLE3.CommandType = CommandType.StoredProcedure;
                    DELETE_TABLE3.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_TempOfftake2"));
                    DELETE_TABLE3.Transaction = tran;
                    DELETE_TABLE3.ExecuteNonQuery();
                   
                    //tạo table @user_TempOfftake2
                    CREATE_TABLE_TempOfftake2.CommandText = "CREATE_TABLE_TempOfftake1";
                    CREATE_TABLE_TempOfftake2.CommandType = CommandType.StoredProcedure;
                    CREATE_TABLE_TempOfftake2.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_TempOfftake2"));
                    CREATE_TABLE_TempOfftake2.Transaction = tran;
                    CREATE_TABLE_TempOfftake2.ExecuteNonQuery();

                    //insert vào table @User_TempOfftake2
                    InsertTableTempOfftake2.CommandText = "InsertTableTempOfftake2";
                    InsertTableTempOfftake2.CommandType = CommandType.StoredProcedure;
                    InsertTableTempOfftake2.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_TempOfftake2"));
                    InsertTableTempOfftake2.Parameters.Add(new SqlParameter("@User_TempOfftake1", Session["username"].ToString() + "_TempOfftake1"));
                    InsertTableTempOfftake2.Transaction = tran;
                    InsertTableTempOfftake2.ExecuteNonQuery();

                    //Cập nhật lại doanh số
                    Update_DoanhSo.CommandText = "Update_DoanhSo";
                    Update_DoanhSo.CommandType = CommandType.StoredProcedure;
                    Update_DoanhSo.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                    Update_DoanhSo.Parameters.Add(new SqlParameter("@User_TempOfftake2", Session["username"].ToString() + "_TempOfftake2"));
                    Update_DoanhSo.Transaction = tran;
                    Update_DoanhSo.ExecuteNonQuery();

                    if (CheckBox1.Checked)
                    {
                        DELETE_TABLE4 = new SqlCommand();
                        DELETE_TABLE4.Connection = con;
                        DELETE_TABLE4.CommandText = "DELETE_TABLE";
                        DELETE_TABLE4.CommandType = CommandType.StoredProcedure;
                        DELETE_TABLE4.Parameters.Add(new SqlParameter("@TableName",  Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi1"));
                        DELETE_TABLE4.Transaction = tran;
                        DELETE_TABLE4.ExecuteNonQuery();

                        CreateTable1 = new SqlCommand();
                        CreateTable1.Connection = con;
                        CreateTable1.CommandText = "CREATE_TABLE_CAY_PHAN_CAP";
                        CreateTable1.CommandType = CommandType.StoredProcedure;
                        CreateTable1.Parameters.Add(new SqlParameter("@TableName", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi1"));
                        CreateTable1.Transaction = tran;
                        CreateTable1.ExecuteNonQuery();

                        INSERT_user_ReportDoanhSoTuyenDuoi1 = new SqlCommand();
                        INSERT_user_ReportDoanhSoTuyenDuoi1.Connection = con;
                        INSERT_user_ReportDoanhSoTuyenDuoi1.CommandText = "INSERT_user_ReportDoanhSoTuyenDuoi1";
                        INSERT_user_ReportDoanhSoTuyenDuoi1.CommandType = CommandType.StoredProcedure;
                        INSERT_user_ReportDoanhSoTuyenDuoi1.Parameters.Add(new SqlParameter("@Table1", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi1"));
                        INSERT_user_ReportDoanhSoTuyenDuoi1.Parameters.Add(new SqlParameter("@Table", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                        INSERT_user_ReportDoanhSoTuyenDuoi1.Transaction = tran;
                        INSERT_user_ReportDoanhSoTuyenDuoi1.ExecuteNonQuery();

                        DELETE_user_ReportDoanhSoTuyenDuoi = new SqlCommand();
                        DELETE_user_ReportDoanhSoTuyenDuoi.Connection = con;
                        DELETE_user_ReportDoanhSoTuyenDuoi.CommandText = "DELETE_user_ReportDoanhSoTuyenDuoi";
                        DELETE_user_ReportDoanhSoTuyenDuoi.CommandType = CommandType.StoredProcedure;
                        DELETE_user_ReportDoanhSoTuyenDuoi.Parameters.Add(new SqlParameter("@Table", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                        DELETE_user_ReportDoanhSoTuyenDuoi.Transaction = tran;
                        DELETE_user_ReportDoanhSoTuyenDuoi.ExecuteNonQuery();

                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2 = new SqlCommand();
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.Connection = con;
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.CommandText = "INSERT_user_ReportDoanhSoTuyenDuoi_LAN2";
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.CommandType = CommandType.StoredProcedure;
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.Parameters.Add(new SqlParameter("@Table1", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi1"));
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.Parameters.Add(new SqlParameter("@Table", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"));
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.Transaction = tran;
                        INSERT_user_ReportDoanhSoTuyenDuoi_LAN2.ExecuteNonQuery();

                    }

                    tran.Commit();
                   
            }
            catch (Exception ex)
            {
                    tran.Rollback();
                    uscMsgBox1.AddMessage(ex.Message, uscMsgBox.enmMessageType.Error);

            }
            finally
            {
                    con.Close();
            }
       
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

        ASPxTreeList1.DataSource = null;
        ASPxTreeList1.DataBind();
        common = new MethodClassCommon();
        try
        {
            string record = common.GetDataDepend("check_tvv", "salesp_key", txtTuVanVien.Text.Trim());
            QuyTrinh9Buoc();
            BindTree();

        }
        catch
        {
            uscMsgBox1.AddMessage("Không tồn tại mã tư vấn viên này", uscMsgBox.enmMessageType.Error);
        }
    }
    void BindTree()
    {
        string vUser = "";
        if (Session["username"].ToString() == "DefaultUser")
        {
            vUser = Session["Salesp_key_Logon"].ToString();
        }
        else
        {
            vUser = Session["username"].ToString();
        }
        try
        {
            ClassExecute.ClassExecute dtex = new ClassExecute.ClassExecute();
            ViewState["db"] = null;
            DataTable db = dtex.Get_Data_DataTable(str,ref error, "DataBind_TreeView", CommandType.StoredProcedure,
                new SqlParameter("@TableName", Session["username"].ToString() + "_ReportDoanhSoTuyenDuoi"),
                new SqlParameter("@username",vUser));
            if (db.Rows.Count > 0)
            {
                ViewState["db"] = db;
                ASPxTreeList1.DataSource = db;
                ASPxTreeList1.DataBind();
                TinhTong();
            }
        }
        catch(Exception e)
        {
            uscMsgBox1.AddMessage(e.Message, uscMsgBox.enmMessageType.Error);
        }
    }  
    protected void Button2_Click(object sender, EventArgs e)
    {
        ASPxTreeList1.ExpandAll();
        ASPxTreeListExporter1.WriteXlsToResponse("PhanCapTVV_" + ASPxTreeList1.Nodes[0]["TVV"].ToString() + "_" + txtFromDate.Text + "_" + txtToDate.Text + ".xls") ;

    }
    protected void ASPxTreeList1_HtmlRowPrepared(object sender, DevExpress.Web.ASPxTreeList.TreeListHtmlRowEventArgs e)
    {

        try
        {
            if ((double.Parse(e.GetValue("ChucVu").ToString()) >= 10) && (double.Parse(e.GetValue("ChucVu").ToString()) < 20))
            {
                e.Row.BackColor = Color.White;
            }
            else if ((double.Parse(e.GetValue("ChucVu").ToString()) >= 20) && (double.Parse(e.GetValue("ChucVu").ToString()) < 30))
            {
                e.Row.BackColor = Color.Wheat;
            }
            else if ((double.Parse(e.GetValue("ChucVu").ToString()) >= 30) && (double.Parse(e.GetValue("ChucVu").ToString()) < 40))
            {
                e.Row.BackColor = Color.AntiqueWhite;
            }
            else if ((double.Parse(e.GetValue("ChucVu").ToString()) >= 40) && (double.Parse(e.GetValue("ChucVu").ToString()) < 50))
            {
                e.Row.BackColor = Color.Violet;
            }
            else if ((double.Parse(e.GetValue("ChucVu").ToString()) >= 50) && (double.Parse(e.GetValue("ChucVu").ToString()) < 60))
            {
                e.Row.BackColor = Color.Turquoise;
            }
            else
            {
                e.Row.BackColor = Color.Teal;
            }
        }
        catch
        {

        }
    }
    protected void ASPxTreeList1_NodeUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
    {
        //try
        //{
        //    dal = new BCDataCenter.DataAccessLayer.DAL();
        //    string error = "";
        //    bool kq = dal.MyExecuteNonQuery("UPDATE_MANGUOIGIOITHIEU", CommandType.StoredProcedure, ref error, new SqlParameter("@salesp_key", e.Keys["salesp_key"].ToString()), new SqlParameter("@recommender", e.NewValues["recommender"].ToString()));
        //    if (kq == true)
        //    {
        //        uscMsgBox1.AddMessage("cập nhật thành công", uscMsgBox.enmMessageType.Success);
        //    }
        //    else
        //    {
        //        uscMsgBox1.AddMessage("cập nhật thất bại", uscMsgBox.enmMessageType.Error);
        //    }
        //}
        //catch(Exception ex)
        //{
        //    uscMsgBox1.AddMessage(ex.Message,uscMsgBox.enmMessageType.Error);
        //}
    }
}
